﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using Labthink.PCSoft.UniversalHost4.Model;
using Labthink.PCSoft.UniversalHost4.Model.Flow;
using Labthink.PCSoft.UniversalHost4.Utility;
using NLog;

namespace Labthink.PCSoft.UniversalHost4.DataAccess
{
    public class MemoryDatabase : IDisposable
    {
        public Logger Log = LogManager.GetCurrentClassLogger();

        /// <summary>
        /// 杯号或者腔号
        /// </summary>
        public int CellOrCup { get; set; }

        /// <summary>
        /// 实时保存
        /// </summary>
        public bool RealtimeSave { get; set; }

        /// <summary>
        /// 批量保存数量
        /// </summary>
        public int SaveCount { get; set; }

        /// <summary>
        /// 最后操作时间
        /// </summary>
        public DateTime LastOperateTime { get; set; }

        /// <summary>
        /// 数据库名称（数据库文件名）
        /// </summary>
        public string DatabaseName { get; set; }

        /// <summary>
        /// 文件小路径
        /// </summary>
        public string CurrentPath { get; set; }

        /// <summary>
        /// 试验类型
        /// </summary>
        public int TestType { get; set; }

        /// <summary>
        /// 腔号（bookID）
        /// </summary>
        public int CurrentCount { get; set; }

        /// <summary>
        /// 试验总览
        /// </summary>
        public TestOverView TestOverViews { get; set; }
        /// <summary>
        /// 试验参数
        /// </summary>
        public List<FullData> Param { get; set; }
        /// <summary>
        /// 试验结果
        /// </summary>
        public DataTable Results { get; set; }
        /// <summary>
        /// 试验数据
        /// </summary>
        public DataTable Datas { get; set; }

        public DataTable Params { get; set; }

        public string OpName { get; set; }

        //private Queue<List<FullData>> _datas;
        //private Thread _thdAddDatas;

        public MemoryDatabase()
        {
            //_datas = new Queue<List<FullData>>();
            //_thdAddDatas = new Thread(new ThreadStart(_addDatas));
            //_thdAddDatas.Start();
        }

        /// <summary>
        /// 添加一条实时数据
        /// </summary>
        /// <param name="data"></param>
        public void AddData(List<FullData> data)
        {
            LastOperateTime = DateTime.Now;
            if (RealtimeSave)
            {
                _updateData(data);
            }
            else
            {
                var dr = Datas.NewRow();
                foreach (var d in data)
                {
                    if (Datas.Columns.Contains(d.ColumnName))
                    {
                        dr[d.ColumnName] = d.Value;
                    }
                }
                Datas.Rows.Add(dr);
                CurrentCount++;
            }
        }

        /// <summary>
        /// 批量保存实时数据
        /// </summary>
        private void _updateDatas(object o)
        {
            Log.Debug("批量保存{0}实时数据{1}条", CurrentPath, CurrentCount);
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                var sql = new StringBuilder();
                var sqlPrm = new StringBuilder();
                var insertData = con.CreateCommand();
                insertData.Transaction = tran;
                foreach (DataColumn col in Datas.Columns)
                {
                    if (col.ColumnName.ToLower() == "id") continue;
                    sql.Append(col.ColumnName).Append(",");
                    sqlPrm.Append("@").Append(col.ColumnName).Append(",");
                    insertData.Parameters.Add(new SQLiteParameter("@" + col.ColumnName, TypeToDbType(col.DataType), col.ColumnName));
                }

                sql.Length--;
                sqlPrm.Length--;
                insertData.CommandText = "INSERT INTO TBL_RealtimeData(" + sql + ") values (" + sqlPrm + ")";
                try
                {
                    SqliteHelper.UpdateDataSet(insertData, Datas);
                    tran.Commit();
                    Datas.Clear();
                    CurrentCount = 0;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"批量保存{0}的{1}条实时数据失败", CurrentPath, CurrentCount);
                }
            }
        }

        /// <summary>
        /// 保存一条实时数据
        /// </summary>
        /// <param name="datas"></param>
        private void _updateData(IEnumerable<FullData> datas)
        {
            //Log.Debug("保存实时数据:{0}", CurrentPath);
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var cmdPrm = con.CreateCommand();
                var sql = new StringBuilder();
                var sqlPrm = new StringBuilder();
                foreach (var fd in datas)
                {
                    sql.Append(fd.ColumnName).Append(",");
                    sqlPrm.Append("@").Append(fd.ColumnName).Append(",");
                    cmdPrm.Parameters.Add(new SQLiteParameter("@" + fd.ColumnName, fd.Value));
                }

                sql.Remove(sql.Length - 1, 1);
                sqlPrm.Remove(sqlPrm.Length - 1, 1);
                cmdPrm.CommandText = "INSERT INTO TBL_RealtimeData (" + sql + ") values (" + sqlPrm + ")";
                var sqlTov = "UPDATE TBL_Overview SET TestEndTime=@TestEndTime";
                var tran = con.BeginTransaction();
                try
                {
                    SqliteHelper.ExecuteNonQuery(tran, cmdPrm);
                    SqliteHelper.ExecuteNonQuery(tran, sqlTov, new object[] { DateTime.Now });
                    tran.Commit();
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"保存{0}数据出错", CurrentPath);
                }
            }
        }

        /// <summary>
        /// 初始化数据 用于实时保存
        /// </summary>
        public void InitData()
        {
            var dr = Results.NewRow();
            for (int i = 0; i < Results.Columns.Count; i++)
            {
                dr[0] = -1;
            }

            Results.Rows.Add(dr);
        }

        /// <summary>
        /// 
        /// </summary>
        public TagAndRemark TagRemark { get; set; }

        /// <summary>
        /// 保存数据库，只调用一次
        /// 需要实时保存的，在试验开始时候保存，否则在整个试验结束时保存（达到设定的次数自动或者用户随时手动保存）
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="testType"></param>
        public TestIndex Save(DataTable dt1, string testType)
        {
            Log.Debug(" 保存数据库{0}", CurrentPath);
            //while (_datas.Count >0)
            //{
            //    Thread.Sleep(500);
            //}
            //实时保存数据,只保存结果和转换关系

            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                var sql = new StringBuilder();
                var sqlPrm = new StringBuilder();

                // 参数
                var insertParam = con.CreateCommand();

                foreach (var fd in Param)
                {
                    if (Params != null && !Params.Columns.Contains(fd.ColumnName))
                    {
                        continue;
                    }
                    sql.Append(fd.ColumnName).Append(",");
                    sqlPrm.Append("@").Append(fd.ColumnName).Append(",");
                    insertParam.Parameters.Add(new SQLiteParameter("@" + fd.ColumnName, fd.Value));
                }

                sql.Length--;
                sqlPrm.Length--;
                insertParam.CommandText = "INSERT INTO TBL_Param(" + sql + ") values (" + sqlPrm + ")";

                // 数据
                var insertData = con.CreateCommand();
                if (Datas != null)
                {
                    sql = new StringBuilder();
                    sqlPrm = new StringBuilder();
                    foreach (DataColumn col in Datas.Columns)
                    {
                        if (col.ColumnName.ToLower() == "id") continue;
                        sql.Append(col.ColumnName).Append(",");
                        sqlPrm.Append("@").Append(col.ColumnName).Append(",");
                        insertData.Parameters.Add(new SQLiteParameter("@" + col.ColumnName, TypeToDbType(col.DataType), col.ColumnName));
                    }

                    sql.Length--;
                    sqlPrm.Length--;
                    insertData.CommandText = "INSERT INTO TBL_RealtimeData(" + sql + ") values (" + sqlPrm + ")";
                }

                // 结果
                var insertResult = con.CreateCommand();
                sql = new StringBuilder();
                sqlPrm = new StringBuilder();
                foreach (DataColumn col in Results.Columns)
                {
                    if (col.ColumnName.ToLower() == "id") continue;
                    sql.Append(col.ColumnName).Append(",");
                    sqlPrm.Append("@").Append(col.ColumnName).Append(",");
                    insertResult.Parameters.Add(new SQLiteParameter("@" + col.ColumnName, TypeToDbType(col.DataType), col.ColumnName));
                }

                sql.Length--;
                sqlPrm.Length--;
                insertResult.CommandText = "INSERT INTO TBL_Result(" + sql + ") values (" + sqlPrm + ")";

                // 总览
                var insertTov = con.CreateCommand();

                insertTov.CommandTimeout = 60;
                insertTov.CommandType = CommandType.Text;
                insertTov.CommandText = "insert into TBL_Overview(TestId,TestStartTime,TestEndTime,EquipmentType1,EquipmentType2,EquipmentYear,EquipmentSerialNo,IsSubTest,DefaultLanguageId,TestType) values (@TestId,@TestStartTime,@TestEndTime,@EquipmentType1,@EquipmentType2,@EquipmentYear,@EquipmentSerialNo,@IsSubTest,@DefaultLanguageId,@TestType)";
                insertTov.Parameters.Add(new SQLiteParameter("@TestId", TestOverViews.TestId));
                insertTov.Parameters.Add(new SQLiteParameter("@TestStartTime", TestOverViews.TestStartTime));
                insertTov.Parameters.Add(new SQLiteParameter("@TestEndTime", TestOverViews.TestStopTime));
                insertTov.Parameters.Add(new SQLiteParameter("@EquipmentType1", TestOverViews.EquipmentType1));
                insertTov.Parameters.Add(new SQLiteParameter("@EquipmentType2", TestOverViews.EquipmentType2));
                insertTov.Parameters.Add(new SQLiteParameter("@EquipmentYear", TestOverViews.EquipmentYear));
                insertTov.Parameters.Add(new SQLiteParameter("@EquipmentSerialNo", TestOverViews.EquipmentSerialNo));
                insertTov.Parameters.Add(new SQLiteParameter("@IsSubTest", TestOverViews.IsSubTest));
                insertTov.Parameters.Add(new SQLiteParameter("@DefaultLanguageId", TestOverViews.DefaultLanguageId));
                insertTov.Parameters.Add(new SQLiteParameter("@TestType", TestOverViews.TestType));

                try
                {
                    // 创建数据库时不插入结果
                    insertResult.Transaction = tran;
                    SqliteHelper.UpdateDataSet(insertResult, Results);
                    if (Datas != null)
                    {
                        insertData.Transaction = tran;
                        SqliteHelper.UpdateDataSet(insertData, Datas);
                    }

                    SqliteHelper.ExecuteNonQuery(tran, insertParam);
                    SqliteHelper.ExecuteNonQuery(tran, insertTov);
                    tran.Commit();
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"保存数据库失败：{0}",  CurrentPath);
                    return null;
                }
            }

            //创建索引
            var ti = new TestIndex
            {
                StartTime = TestOverViews.TestStartTime,
                EndTime = TestOverViews.TestStopTime,
                FileName = DatabaseName,
                //FilePath = CurrentPath,
                //TestTypeName = testType,
                TestType = TestOverViews.TestType,
                TestId = TestOverViews.TestId,
                ESignal = 0
            };

            var data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "testname");
            ti.TestName = data.Value == null ? "" : data.Value.ToString();

            data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "samplename");
            if (data != null)
            {
                ti.SampleName = data.Value == null ? "" : data.Value.ToString();
            }

            data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "samplematerial");
            if (data != null)
            {
                ti.SampleMaterial = data.Value == null ? "" : data.Value.ToString();
            }

            data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "sampleno");
            if (data != null)
            {
                ti.SampleNo = data.Value == null ? "" : data.Value.ToString();
            }

            data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "samplebatch");
            if (data != null)
            {
                ti.SampleBatch = data.Value == null ? "" : data.Value.ToString();
            }

            data = Param.SingleOrDefault(d => d.ColumnName.ToLower() == "opname");
            if (data != null)
            {
                ti.OpName = data.Value == null ? "" : data.Value.ToString();
            }

            if (TagRemark != null)
            {
                ti.Tag = TagRemark.Tag;
                ti.Remark = TagRemark.Remark;
            }

            return ti;
        }

        /// <summary>
        /// 类型转换
        /// </summary>
        /// <returns></returns>
        private DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name, true);
            }
            catch
            {
                dbt = DbType.Object;
            }

            return dbt;
        }

        /// <summary>
        /// 更新参数
        /// </summary>
        /// <param name="param"></param>
        public int UpdateParam(List<FullData> param)
        {
            Log.Debug("更新参数：{0}", CurrentPath);
            Param = param.Clone();
            LastOperateTime = DateTime.Now;
            OpName = Param.Single(p => p.DataNo == 10073).Value.ToString();
            if (RealtimeSave)
            {
                return _updateParam();
            }
            return 1;
        }

        public string GetOpName()
        {
            return Param.Single(p => p.DataNo == 10073).Value.ToString();
        }

        /// <summary>
        /// 删除某个并列试验，包括实时数据和结果，以及并列试验id相关的数据。
        /// </summary>
        /// <param name="subTestId"></param>
        /// <returns></returns>
        public int DeleteSubTest(int subTestId)
        {
            //删除内存数据库
            int i = 0;
            for (i = 0; i < Datas.Rows.Count;)
            {
                if (Datas.Rows[i]["SubTestId"].ToInt32() == subTestId)
                {
                    Datas.Rows.RemoveAt(i);
                    CurrentCount--;
                }
                else
                {
                    i++;
                }
            }

            for (i = 0; i < Results.Rows.Count;)
            {
                if (Results.Rows[i]["SubTestId"].ToInt32() == subTestId)
                {
                    Results.Rows.RemoveAt(i);
                }
                else
                {
                    i++;
                }
            }

            //删除硬盘数据库
            string str;
            str = SqliteHelper.CreateConnectionString(CurrentPath);
            using (var con = new SQLiteConnection(str))
            {
                con.Open();

                var sqlTov = $"delete from TBL_RealTimeData where SubTestId = {subTestId}";
                var tran = con.BeginTransaction();
                try
                {

                    int r = SqliteHelper.ExecuteNonQuery(tran, sqlTov, null);
                    tran.Commit();

                    return 1;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"删除并列试验出错：{0}", e.Message);
                    return -1;
                }
            }
        }

        private int _updateParam()
        {
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var sql = new StringBuilder("UPDATE TBL_Param SET ");
                var insertParam = con.CreateCommand();

                foreach (var fd in Param)
                {
                    sql.Append(fd.ColumnName).Append("=").Append(fd.ColumnName).Append(",");
                    insertParam.Parameters.Add(new SQLiteParameter("@" + fd.ColumnName, fd.Value));
                }

                insertParam.CommandText = sql.Remove(sql.Length - 1, 1).ToString();

                var sqlTov = "UPDATE TBL_Overview SET TestEndTime=@TestEndTime";
                var tran = con.BeginTransaction();
                try
                {
                    SqliteHelper.ExecuteNonQuery(tran, insertParam);
                    SqliteHelper.ExecuteNonQuery(tran, sqlTov, new object[] { DateTime.Now });
                    tran.Commit();
                    return 1;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"更新{0}参数数据出错", CurrentPath);
                    return -1;
                }
            }
        }

        /// <summary>
        /// 添加一条结果数据
        /// </summary>
        /// <param name="result"></param>
        public void AddResult(List<FullData> result)
        {
            Log.Debug("添加{0}的试验结果", CurrentPath);
            if (RealtimeSave)
            {
                _updateResult(result);
            }
            else
            {
                var dr = Results.NewRow();
                foreach (var d in result)
                {
                    if (Results.Columns.Contains(d.ColumnName))
                    {
                        dr[d.ColumnName] = d.Value;
                    }
                }

                Results.Rows.Add(dr);
            }

            if (CurrentCount >= SaveCount)
            {
                _updateDatas(null);
            }
        }

        private void _updateResult(IReadOnlyCollection<FullData> result)
        {
            //查找10034的数据列
            var rd = result.SingleOrDefault(d => d.DataNo == 10034);
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var deleteSql = "";
                if (rd != null)
                {
                    deleteSql = $"DELETE FROM TBL_Result WHERE {rd.ColumnName}={rd.Value};";
                    deleteSql += $"DELETE FROM TBL_Result WHERE {rd.ColumnName} is null;";
                    deleteSql += "DELETE FROM sqlite_sequence WHERE name = 'TBL_Result';";
                }

                //增加一条结果
                var cmdPrm = con.CreateCommand();
                var sql = new StringBuilder();
                var sqlPrm = new StringBuilder();
                foreach (var fd in result)
                {
                    sql.Append(fd.ColumnName).Append(",");
                    sqlPrm.Append("@").Append(fd.ColumnName).Append(",");
                    cmdPrm.Parameters.Add(new SQLiteParameter("@" + fd.ColumnName, fd.Value));
                }

                sql.Length--;
                sqlPrm.Length--;
                cmdPrm.CommandText = "INSERT INTO TBL_Result (" + sql + ") values (" + sqlPrm + ")";
                var sqlTov = "UPDATE TBL_Overview SET TestEndTime=@TestEndTime";
                var tran = con.BeginTransaction();
                try
                {
                    if (!string.IsNullOrEmpty(deleteSql))
                    {
                        SqliteHelper.ExecuteNonQuery(tran, deleteSql, null);
                    }

                    SqliteHelper.ExecuteNonQuery(tran, cmdPrm);
                    SqliteHelper.ExecuteNonQuery(tran, sqlTov, new object[] { DateTime.Now });
                    tran.Commit();
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"保存{0}结果数据出错", CurrentPath);
                }
            }
        }

        /// <summary>
        /// 修改表信息结构
        /// </summary>
        /// <param name="type"></param>
        /// <param name="datas"></param>
        public void ChangeStruct(InfoType type, List<FullData> datas)
        {
            string tn = "";
            string typeName = "";
            switch (type)
            {
                case InfoType.Param:
                    tn = "TBL_ParamInfo";
                    typeName = "参数";
                    break;

                case InfoType.Data:
                    tn = "TBL_RealtimeDataInfo";
                    typeName = "实时数据";
                    break;

                case InfoType.Result:
                    tn = "TBL_ResultInfo";
                    typeName = "结果";
                    break;
            }

            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(CurrentPath)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    foreach (var data in datas)
                    {
                        var sql = new StringBuilder("UPDATE ");
                        sql.Append(tn);
                        sql.Append("  SET ");
                        var os = new List<object>();
                        if (!string.IsNullOrEmpty(data.LanguageId))
                        {
                            sql.Append(" LanguageId=@LanguageId,");
                            os.Add(data.LanguageId);
                        }

                        if (!string.IsNullOrEmpty(data.Unit))
                        {
                            sql.Append(" Unit=@Unit,");
                            os.Add(data.Unit);
                        }

                        if (data.Precision > 0)
                        {
                            sql.Append(" Precision=@Precision,");
                            os.Add(data.Precision);
                        }

                        if (os.Count > 0)
                        {
                            var strSql = $"{sql.Remove(sql.Length - 1, 1)} WHERE DataNO=@DataNO";
                            os.Add(data.DataNo);
                            SqliteHelper.ExecuteNonQuery(tran, strSql, os.ToArray());
                        }
                    }

                    tran.Commit();
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e,"更改 {0} 结构失败", typeName);
                }
            }
        }


        #region IDisposable 成员

        public void Dispose()
        {
            Param.Clear();
            Results.Dispose();
            Datas.Dispose();
            //try
            //{
            //    _thdAddDatas.Abort();
            //    _thdAddDatas = null;
            //    _datas = null;
            //}
            //catch
            //{
            //}

        }


        #endregion
    }

    /// <summary>
    /// 试验总览
    /// </summary>
    public class TestOverView : ICloneable
    {
        /// <summary>
        /// 自增主键
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 试验编号
        /// </summary>
        public int TestId { get; set; }

        /// <summary>
        /// 试验开始时间
        /// </summary>
        public DateTime TestStartTime { get; set; }

        /// <summary>
        /// 实验结束时间
        /// </summary>
        public DateTime TestStopTime { get; set; }

        /// <summary>
        /// 设备型号
        /// </summary>
        public string EquipmentType1 { get; set; }

        /// <summary>
        /// 设备型号
        /// </summary>
        public string EquipmentType2 { get; set; }

        /// <summary>
        /// 设备出厂年份
        /// </summary>
        public string EquipmentYear { get; set; }

        /// <summary>
        /// 设备序列号
        /// </summary>
        public int EquipmentSerialNo { get; set; }

        /// <summary>
        /// 是否子试验
        /// </summary>
        public int IsSubTest { get; set; }

        /// <summary>
        /// 默认语言编号
        /// </summary>
        public int DefaultLanguageId { get; set; }

        /// <summary>
        /// 试验类型
        /// </summary>
        public int TestType { get; set; }


        #region ICloneable 成员

        public object Clone()
        {
            return new TestOverView
            {
                Id = Id,
                TestId = TestId,
                TestStartTime = TestStartTime,
                TestStopTime = TestStopTime,
                EquipmentType1 = EquipmentType1,
                EquipmentType2 = EquipmentType2,
                EquipmentYear = EquipmentYear,
                EquipmentSerialNo = EquipmentSerialNo,
                IsSubTest = IsSubTest,
                DefaultLanguageId = DefaultLanguageId,
                TestType = TestType
            };

        }

        #endregion
    }

    public enum InfoType
    {
        Param,
        Data,
        Result
    }
}
